﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using GatherWebLib.Model;
using System.Data.OleDb;
using System.Data;
using System.IO;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using MySql.Data.MySqlClient;

namespace GatherWebLib.publish
{
   public class cPublishTask
   {
       #region 属性

       public cPublishTask_m m_pTaskData;
     
       public cPublishTask()
       {
           m_pTaskData = new cPublishTask_m();

       }

       private cGlobalParas.PublishType PublishType
       {
           get { return m_pTaskData.PublishType; }
       }
       #endregion


     
       public void startPublic()
       {
           try
           {
               ExportData();
           }
           catch (System.Exception ex)
           {
               e_PublishError(this, new PublishErrorEventArgs(m_pTaskData.TaskID, m_pTaskData.TaskName, ex));
           }
       }

       #region 导出数据 支持文本 Excel Access
       private bool ExportData()
       {
           //触发发布启动事件
           PublishStartedEventArgs evt = new PublishStartedEventArgs(m_pTaskData.TaskID, m_pTaskData.TaskName);
           e_PublishStarted(this, evt);

           switch (this.PublishType)
           {
               case cGlobalParas.PublishType.PublishAccess:
                   ExportAccess();
                   break;
               case cGlobalParas.PublishType.PublishExcel:
                   ExportExcel();
                   break;
               case cGlobalParas.PublishType.PublishTxt:
                   ExportTxt();
                   break;
               case cGlobalParas.PublishType.PublishMSSql:
                   ExportMSSql();
                   break;
               case cGlobalParas.PublishType.PublishMySql:
                   ExportMySql();
                   break;
               //case cGlobalParas.PublishType.PublishWeb:
               //    ExportWeb();
               //    break;
               default:
                   break;
           }

           PublishCompletedEventArgs evt1 = new PublishCompletedEventArgs(m_pTaskData.TaskID, m_pTaskData.TaskName);

           e_PublishCompleted(this, evt1);

          

           return true;
       }

     

       private string getCreateTablesql(cGlobalParas.DatabaseType dType, string Encoding)
       {
           string strsql = "";

           strsql = "create table " + this.m_pTaskData.DataTableName + "(";
           for (int i = 0; i < m_pTaskData.PublishData.Columns.Count; i++)
           {
               switch (dType)
               {
                   case cGlobalParas.DatabaseType.Access:
                       strsql += m_pTaskData.PublishData.Columns[i].ColumnName + " " + "text" + ",";
                       break;
                   case cGlobalParas.DatabaseType.MSSqlServer:
                       strsql += m_pTaskData.PublishData.Columns[i].ColumnName + " " + "text" + ",";
                       break;
                   case cGlobalParas.DatabaseType.MySql:
                       strsql += m_pTaskData.PublishData.Columns[i].ColumnName + " " + "text" + ",";
                       break;
                   default:
                       strsql += m_pTaskData.PublishData.Columns[i].ColumnName + " " + "text" + ",";
                       break;
               }
           }
           strsql = strsql.Substring(0, strsql.Length - 1);
           strsql += ")";

           //如果是mysql数据库，需要根据连接串的字符集进行数据表的建立
           if (dType == cGlobalParas.DatabaseType.MySql)
           {
               if (Encoding == "" || Encoding == null)
                   Encoding = "utf8";

               strsql += " CHARACTER SET " + Encoding + " ";
           }

           return strsql;
       }

       private void ExportAccess()
       {
           bool IsTable = false;

           OleDbConnection conn = new OleDbConnection();

           string connectionstring = m_pTaskData.DataSource;

           //判断是否为新建表
           string tName = m_pTaskData.DataTableName;

           conn.ConnectionString = connectionstring;

           try
           {
               conn.Open();
           }
           catch (System.Exception ex)
           {
               if (e_PublishLog != null)
               {

                   e_PublishLog(this, new PublishLogEventArgs(this.m_pTaskData.TaskID, ((int)cGlobalParas.LogType.Error).ToString() + "任务：" + this.m_pTaskData.TaskName + "发布Access失败，错误信息为：" + ex.Message + "\n"));
               }

               return;
           }

           System.Data.DataTable tb = conn.GetSchema("Tables");

           foreach (DataRow r in tb.Rows)
           {
               if (r[3].ToString() == "TABLE")
               {
                   if (r[2].ToString() == tName)
                   {
                       IsTable = true;
                       break;
                   }
               }

           }

           if (IsTable == false)
           {
               //需要建立新表，建立新表的时候采用ado.net新建行的方式进行数据增加
               string CreateTablesql = getCreateTablesql(cGlobalParas.DatabaseType.Access, "");

               OleDbCommand com = new OleDbCommand();
               com.Connection = conn;
               com.CommandText = CreateTablesql;
               com.CommandType = CommandType.Text;
               try
               {
                   int result = com.ExecuteNonQuery();
               }
               catch (System.Data.OleDb.OleDbException ex)
               {
                   if (ex.ErrorCode != -2147217900)
                   {
                       if (e_PublishLog != null)
                       {

                           e_PublishLog(this, new PublishLogEventArgs(m_pTaskData.TaskID, ((int)cGlobalParas.LogType.Error).ToString() + "任务：" + m_pTaskData.TaskName + "发布MySql失败，错误信息为：" + ex.Message + "\n"));
                       }

                       throw ex;
                   }
               }

               try
               {

                   System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " + tName, conn);
                   System.Data.OleDb.OleDbCommandBuilder builder = new System.Data.OleDb.OleDbCommandBuilder(da);

                   DataSet ds = new DataSet();
                   da.Fill(ds, m_pTaskData.DataTableName);

                   for (int i = 0; i < m_pTaskData.PublishData.Rows.Count; i++)
                   {
                       DataRow dr = ds.Tables[0].NewRow();
                       for (int j = 0; j < m_pTaskData.PublishData.Columns.Count; j++)
                       {
                           dr[j] = m_pTaskData.PublishData.Rows[i][j].ToString();
                       }
                       ds.Tables[0].Rows.Add(dr);
                   }
                   int m = da.Update(ds.Tables[0]);
               }
               catch (System.Exception ex)
               {
                   if (e_PublishLog != null)
                   {
                     
                       e_PublishLog(this, new PublishLogEventArgs(m_pTaskData.TaskID, ((int)cGlobalParas.LogType.Error).ToString() + "任务：" + m_pTaskData.TaskName + "发布Access失败，错误信息为：" + ex.Message + "\n"));
                   }

                   return;
               }

           }
           else
           {
               try
               {
                   //无需建立新表，需要采用sql语句的方式进行，但需要替换sql语句中的内容
                   System.Data.OleDb.OleDbCommand cm = new System.Data.OleDb.OleDbCommand();
                   cm.Connection = conn;
                   cm.CommandType = CommandType.Text;

                   //开始拼sql语句
                   string sql = "";

                   for (int i = 0; i < m_pTaskData.PublishData.Rows.Count; i++)
                   {
                       sql = m_pTaskData.Sql;

                       for (int j = 0; j < m_pTaskData.PublishData.Columns.Count; j++)
                       {
                           string strPara = "{" + m_pTaskData.PublishData.Columns[j].ColumnName + "}";
                           //string strParaValue = m_pTaskData.PublishData.Rows[i][j].ToString();
                           string strParaValue = m_pTaskData.PublishData.Rows[i][j].ToString().Replace("\"", "\"\"");
                           sql = sql.Replace(strPara, strParaValue);
                       }

                       cm.CommandText = sql;
                       cm.ExecuteNonQuery();
                   }
               }
               catch (System.Exception ex)
               {
                   if (e_PublishLog != null)
                   {
                       
                       e_PublishLog(this, new PublishLogEventArgs(m_pTaskData.TaskID, ((int)cGlobalParas.LogType.Error).ToString() + "任务：" + m_pTaskData.TaskName + "发布Access失败，错误信息为：" + ex.Message + "\n"));
                   }

                   return;
               }

           }


           conn.Close();
       }

       private void ExportExcel()
       {
           string TaskName = m_pTaskData.TaskName;
           string FileName = m_pTaskData.ExportFileName;
           System.Data.DataTable gData = m_pTaskData.PublishData;

           //判断目录根据结果并创建
           cTool.CreateDirectory(FileName);

           // 定义要使用的Excel 组件接口
           // 定义Application 对象,此对象表示整个Excel 程序

           FileStream myStream = File.Open(FileName, FileMode.Create, FileAccess.Write, FileShare.Write);
           StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));
           string str = "";
           string tempStr = "";
           int i = 0;
           int Count = 0;

           try
           {
               if (m_pTaskData.IsExportHeader == true)
               {
                   //写标题 
                   for (i = 0; i < gData.Columns.Count; i++)
                   {
                       str += "\t";
                       str += gData.Columns[i].ColumnName;
                   }

                   sw.WriteLine(str);
               }

               Count = gData.Rows.Count;
               //写内容 
               for (i = 0; i < gData.Rows.Count; i++)
               {
                   for (int j = 0; j < gData.Columns.Count; j++)
                   {

                       tempStr += "\t";
                       tempStr += gData.Rows[i][j];
                   }
                   sw.WriteLine(tempStr);
                   tempStr = "";

               }


               sw.Close();
               myStream.Close();

           }
           catch (Exception ex)
           {
               if (e_PublishLog != null)
               {
                 
                   e_PublishLog(this, new PublishLogEventArgs(m_pTaskData.TaskID, ((int)cGlobalParas.LogType.Error).ToString() + "任务：" + m_pTaskData.TaskName + "发布" + FileName + "失败，错误信息为：" + ex.Message + "\n"));
               }

               return;
           }
           finally
           {
               sw.Close();
               myStream.Close();

           }

           return;
       }

       private void ExportTxt()
       {
           string TaskName = m_pTaskData.TaskName;
           string FileName = m_pTaskData.ExportFileName;
           System.Data.DataTable gData = m_pTaskData.PublishData;

           //判断目录根据结果并创建
           cTool.CreateDirectory(FileName);


           FileStream myStream = File.Open(FileName, FileMode.Create, FileAccess.Write, FileShare.Write);
           StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));
           string str = "";
           string tempStr = "";

           try
           {
               if (this.m_pTaskData.IsExportHeader == true)
               {
                   //写标题 
                   for (int i = 0; i < gData.Columns.Count; i++)
                   {
                       str += "\t";
                       str += gData.Columns[i].ColumnName;
                   }

                   sw.WriteLine(str);
               }

               //写内容 
               for (int i = 0; i < gData.Rows.Count; i++)
               {
                   for (int j = 0; j < gData.Columns.Count; j++)
                   {

                       tempStr += "\t";
                       tempStr += gData.Rows[i][j];
                   }
                   sw.WriteLine(tempStr);
                   tempStr = "";
               }


               sw.Close();
               myStream.Close();

           }
           catch (Exception ex)
           {
               if (e_PublishLog != null)
               {
                  
                   e_PublishLog(this, new PublishLogEventArgs(m_pTaskData.TaskID, ((int)cGlobalParas.LogType.Error).ToString() + "任务：" + m_pTaskData.TaskName + "发布" + FileName + "失败，错误信息为：" + ex.Message + "\n"));
               }

               return;
           }
           finally
           {
               sw.Close();
               myStream.Close();
           }


           return;

       }

       private void ExportMSSql()
       {
           bool IsTable = false;

           SqlConnection conn = new SqlConnection();

           string connectionstring = m_pTaskData.DataSource;

           //判断是否为新建表
           string tName = m_pTaskData.DataTableName;

           conn.ConnectionString = connectionstring;

           try
           {
               conn.Open();
           }
           catch (System.Exception ex)
           {
               if (e_PublishLog != null)
               {
                  
                   e_PublishLog(this, new PublishLogEventArgs(m_pTaskData.TaskID, ((int)cGlobalParas.LogType.Error).ToString() + "任务：" + m_pTaskData.TaskName + "发布MSSql失败，错误信息为：" + ex.Message + "\n"));
               }

               throw ex;
           }

           //System.Data.DataTable tb = conn.GetSchema("Tables");

           //foreach (DataRow r in tb.Rows)
           //{
           //    if (r[2].ToString() == tName)
           //    {
           //        IsTable = true;
           //        break;
           //    }
           //}

           //if (IsTable == false)
           //{
           //    //需要建立新表，建立新表的时候采用ado.net新建行的方式进行数据增加
           //    string CreateTablesql = getCreateTablesql(cGlobalParas.DatabaseType.MSSqlServer, "");

           //    SqlCommand com = new SqlCommand();
           //    com.Connection = conn;
           //    com.CommandText = CreateTablesql;
           //    com.CommandType = CommandType.Text;
           //    try
           //    {
           //        int result = com.ExecuteNonQuery();
           //    }
           //    catch (System.Data.SqlClient.SqlException ex)
           //    {
           //        if (ex.ErrorCode != -2147217900)
           //        {
           //            if (e_PublishLog != null)
           //            {
                          
           //                e_PublishLog(this, new PublishLogEventArgs(m_pTaskData.TaskID, ((int)cGlobalParas.LogType.Error).ToString() + "任务：" + m_pTaskData.TaskName + "发布MSSql失败，错误信息为：" + ex.Message + "\n"));
           //            }

           //            throw ex;
           //        }
           //    }



           //}
           //else
           //{
           //无需建立新表，需要采用sql语句的方式进行，但需要替换sql语句中的内容
           SqlCommand cm = new SqlCommand();
           cm.Connection = conn;
           cm.CommandType = CommandType.Text;

           //开始拼sql语句
           string strInsertSql = m_pTaskData.Sql;

           //需要将双引号替换成单引号
           //strInsertSql = strInsertSql.Replace("\"", "'");

           string sql = "";

           for (int i = 0; i < m_pTaskData.PublishData.Rows.Count; i++)
           {
               sql = strInsertSql;

               for (int j = 0; j < m_pTaskData.PublishData.Columns.Count; j++)
               {
                   string strPara = "{" + m_pTaskData.PublishData.Columns[j].ColumnName + "}";
                   //string strParaValue = m_pTaskData.PublishData.Rows[i][j].ToString();
                   string strParaValue = m_pTaskData.PublishData.Rows[i][j].ToString().Replace("\"", "\"\"");
                   sql = sql.Replace(strPara, strParaValue);
               }
               try
               {
                   cm.CommandText = sql;
                   cm.ExecuteNonQuery();
               }
               catch (System.Exception ex)
               {
                   if (e_PublishLog != null)
                   {
                      
                       e_PublishLog(this, new PublishLogEventArgs(m_pTaskData.TaskID, ((int)cGlobalParas.LogType.Error).ToString() + "任务：" + m_pTaskData.TaskName + "发布MSSql失败，Sql语句为：" + sql.ToString() + " 错误信息为：" + ex.Message + "\n"));
                   }

               }
           }
           //}
           conn.Close();
       }

       private void ExportMySql()
       {
           bool IsTable = false;

           MySqlConnection conn = new MySqlConnection();

           string connectionstring = m_pTaskData.DataSource;

           //判断是否为新建表
           string tName = m_pTaskData.DataTableName;

           conn.ConnectionString = connectionstring;

           try
           {
               conn.Open();
           }
           catch (System.Exception ex)
           {
               if (e_PublishLog != null)
               {
                  
                   e_PublishLog(this, new PublishLogEventArgs(m_pTaskData.TaskID, ((int)cGlobalParas.LogType.Error).ToString() + "任务：" + m_pTaskData.TaskName + "发布MySql失败，错误信息为：" + ex.Message + "\n"));
               }
               throw ex;
           }

           System.Data.DataTable tb = conn.GetSchema("Tables");

           foreach (DataRow r in tb.Rows)
           {
               if (string.Compare(r[2].ToString(), tName, true) == 0)
               {
                   IsTable = true;
                   break;
               }
           }

           if (IsTable == false)
           {
               //通过连接字符串把编码获取出来，根据编码进行数据表的建立
               string strMatch = "(?<=character set=)[^\\s]*(?=[\\s;])";
               Match s = Regex.Match(connectionstring, strMatch, RegexOptions.IgnoreCase);
               string Encoding = s.Groups[0].Value;

               //需要建立新表，建立新表的时候采用ado.net新建行的方式进行数据增加
               string CreateTablesql = getCreateTablesql(cGlobalParas.DatabaseType.MySql, Encoding);

               MySqlCommand com = new MySqlCommand();
               com.Connection = conn;
               com.CommandText = CreateTablesql;
               com.CommandType = CommandType.Text;
               try
               {
                   int result = com.ExecuteNonQuery();
               }
               catch (MySql.Data.MySqlClient.MySqlException ex)
               {
                   if (ex.ErrorCode != -2147217900)
                   {
                       if (e_PublishLog != null)
                       {
                           
                           e_PublishLog(this, new PublishLogEventArgs(m_pTaskData.TaskID, ((int)cGlobalParas.LogType.Error).ToString() + "任务：" + m_pTaskData.TaskName + "发布MySql失败，错误信息为：" + ex.Message + "\n"));
                       }
                       throw ex;
                   }
               }
           }




           //无需建立新表，需要采用sql语句的方式进行，但需要替换sql语句中的内容
           MySqlCommand cm = new MySqlCommand();
           cm.Connection = conn;
           cm.CommandType = CommandType.Text;

           //开始拼sql语句
           string strInsertSql = m_pTaskData.Sql;

           //需要将双引号替换成单引号
           //strInsertSql = strInsertSql.Replace("\"", "'");


           string sql = "";



           for (int i = 0; i < m_pTaskData.PublishData.Rows.Count; i++)
           {
               sql = strInsertSql;

               for (int j = 0; j < m_pTaskData.PublishData.Columns.Count; j++)
               {
                   string strPara = "{" + m_pTaskData.PublishData.Columns[j].ColumnName + "}";
                   string strParaValue = m_pTaskData.PublishData.Rows[i][j].ToString().Replace("\"", "\"\"");
                   sql = sql.Replace(strPara, strParaValue);
               }

               try
               {
                   cm.CommandText = sql;
                   cm.ExecuteNonQuery();
               }
               catch (System.Exception ex)
               {
                   if (e_PublishLog != null)
                   {
                    
                       e_PublishLog(this, new PublishLogEventArgs(m_pTaskData.TaskID, ((int)cGlobalParas.LogType.Error).ToString() + "任务：" + m_pTaskData.TaskName + "发布MySql失败，Sql语句为：" + sql.ToString() + " 错误信息为：" + ex.Message + "\n"));
                   }

               }
           }

           conn.Close();
       }

      

       #endregion


       #region 事件

       private readonly Object m_eventLock = new Object();

       /// 采集任务开始事件
       private event EventHandler<PublishStartedEventArgs> e_PublishStarted;
       public event EventHandler<PublishStartedEventArgs> PublishStarted
       {
           add { lock (m_eventLock) { e_PublishStarted += value; } }
           remove { lock (m_eventLock) { e_PublishStarted -= value; } }
       }

       /// 采集任务错误事件
       private event EventHandler<PublishErrorEventArgs> e_PublishError;
       public event EventHandler<PublishErrorEventArgs> PublishError
       {
           add { lock (m_eventLock) { e_PublishError += value; } }
           remove { lock (m_eventLock) { e_PublishError -= value; } }
       }

       //发布日志事件
       private event EventHandler<PublishLogEventArgs> e_PublishLog;
       public event EventHandler<PublishLogEventArgs> PublishLog
       {
           add { lock (m_eventLock) { e_PublishLog += value; } }
           remove { lock (m_eventLock) { e_PublishLog -= value; } }
       }
       /// 采集任务完成事件
       private event EventHandler<PublishCompletedEventArgs> e_PublishCompleted;
       public event EventHandler<PublishCompletedEventArgs> PublishCompleted
       {
           add { lock (m_eventLock) { e_PublishCompleted += value; } }
           remove { lock (m_eventLock) { e_PublishCompleted -= value; } }
       }
       
       #endregion
   }
}
